package edu.zut.cs.network.merhandise.goods.io.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import edu.zut.cs.network.merhandise.goods.io.FileIO;

public class FileIOImpl implements FileIO {
	protected Logger logger = LogManager.getLogger(this.getClass().getName());
		Connection conn;
		PreparedStatement st;
		ResultSet rs;
		private String driver;
		private String url;
		private String user;
		private String pass;

		public FileIOImpl() {
			try {
				this.initParam("mysql.ini");
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (IOException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				try {
					Class.forName(driver);
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				conn = DriverManager.getConnection(url, user, pass);
				st = conn.prepareStatement("INSERT INTO T_MERHANDISE VALUE(NULL,?,?,?)");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		public void initParam(String paramfile) throws FileNotFoundException, IOException {
			Properties props = new Properties();
			props.load(new FileInputStream(paramfile));
			driver = props.getProperty("driver");
			url = props.getProperty("url");
			user = props.getProperty("user");
			pass = props.getProperty("pass");
		}

		public void leadingIn(String fileName) throws FileNotFoundException {

			File file = new File(fileName);
			InputStream input = new FileInputStream(file);
			XSSFWorkbook wb = null;
			try {
				wb = new XSSFWorkbook(input);
			} catch (IOException e) {
				e.printStackTrace();
			}
			XSSFSheet sheet = wb.getSheetAt(1);
			try {
				for (int i = 1; i <= sheet.getLastRowNum(); i++) {
					Row row = sheet.getRow(i);
					for (int j = 0; j < row.getLastCellNum(); j++) {
						Cell cell = row.getCell(j);
						if (cell != null) {
							cell.setCellType(CellType.STRING);
							String value = String.valueOf(row.getCell(j).getStringCellValue());
							if (j == 0)
								st.setString(j+1, value);
							if (j == 1)
								st.setString(j+1, value);
							if (j == 2)
								st.setString(j+1, value);
						}
					}
					st.executeUpdate();
					
				}
				this.logger.info("导入数据库成功！");
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (st != null) {
						st.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}


	public void leadingOut() throws FileNotFoundException, IOException, SQLException {
		// TODO Auto-generated method stub
		this.initParam("mysql.ini");
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pass);
			rs = st.executeQuery("SELECT ID ,NAME,PRICE, SALES FROM T_MERHANDISE");
			while (rs.next()) {
				this.logger.info("id="+rs.getString(1)+ "\nname=" + rs.getString(2) + "\nprice=" + rs.getString(3) + "\nsales="
						+ rs.getString(4)+"\n\n");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			rs.close();
			st.close();
			conn.close();///////////////
		}
	}

}
